package com.example.mapper;

import com.example.pojo.Dormitory;
import com.github.pagehelper.Page;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface DormitoryMapper {

    @Insert("insert into Dormitory (dormitory_id, category_id, create_user, create_time, update_time) " +
            "values (#{dormitoryId},#{categoryId},#{createUser},#{createTime},#{updateTime})")
    void addDormitory(Dormitory dormitory);

    @Update("update dormitory set dormitory_id = #{dormitoryId},category_id = #{categoryId},update_time= #{updateTime} where id = #{id}")
    void updateDormitory(Dormitory dormitory);

    @Select("select * from dormitory where id = #{id}")
    Dormitory getDormitoryById(Integer id);

    @Delete("delete from dormitory where id = #{id}")
    void deleteDormitory(Integer id);

/*    @Select("select * from dormitory where id = #{id}")*/
    Page<Dormitory> listDormitory(@Param("id") Integer id,@Param("categoryId") Integer categoryId);

    @Select("select * from dormitory where create_user = #{id}")
    List<Dormitory> list(Integer id);


    @Select("select * from dormitory")
    List<Dormitory> findAll();

    @Select("select * from dormitory where category_id = 1")
    List<Dormitory> findByZhiYe();

    @Select("select * from dormitory where category_id = 2")
    List<Dormitory> findShuangChuangSiRen();

    @Select("select * from dormitory where category_id = 4")
    List<Dormitory> findShuangChuangShuangRen();

    @Select("select * from dormitory where category_id = 5")
    List<Dormitory> findQiYiSiRen();

    @Select("select * from dormitory where category_id = 6")
    List<Dormitory> findByYaoLinYaoSiRen();

    @Select("select * from dormitory where category_id = 8")
    List<Dormitory> findByShiJiuSiRen();

    @Select("select *\n" +
            "from dormitory\n" +
            "where dormitory_id in (select x.dormitory_id\n" +
            "                       from (select u.dormitory_id,count(u.dormitory_id) as cnt\n" +
            "                             from user u\n" +
            "                                      join\n" +
            "                                  (select d.dormitory_id\n" +
            "                                   from dormitory d\n" +
            "                                   where category_id = 1) as d\n" +
            "                                  on u.dormitory_id = d.dormitory_id\n" +
            "                             group by u.dormitory_id\n" +
            "                             having cnt < 4) as x)\n" +
            "and category_id = 1\n" +
            "\n")
    List<Dormitory> findExcludeFullByZhiYe();
}
